package com.cn.ccp.service.system;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;

import com.cn.ccp.action.BaseAction;
import com.cn.ccp.bean.ResourseBean;
import com.cn.ccp.bean.UserBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.service.resourse.ResourseService.ResourseMapper;
import com.cn.ccp.util.CCPContants;
import com.cn.ccp.util.Contants;
import com.cn.ccp.util.MsgConstants;

public class TeacherService extends BasicService {
	
	private Log log = LogFactory.getLog(getClass());
	
	public int sava(final UserBean userBean,final List teachGroupList) {
		final int index = CCPContants.getClobalIndex();
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					int tmpid = CCPContants.getClobalIndex();
					String year = "" ;
					try {
						year = ""+(Integer.parseInt(CCPContants.sdf.format(CCPContants.sdf.parse(userBean.getAdmissionTime())))+ CCPContants.undergraduate_year );
					} catch (Exception e) {
						e.printStackTrace();
						log.error("sava user  AdmissionTime failure");
						CCPContants.tranmitResultMap.put(index, -1);
						return false; 
					} 
					String faculty = ""+CCPContants.valueTransitionTo2(Integer.parseInt(userBean.getFacultyId()));
//					String specialty = CCPContants.valueTransitionTo2(Integer.parseInt(userBean.getSpecialtyId()));
					
					userBean.setNumber(""+tmpid);
					
					String passwordSql = "select password(?)";
					String md5Password = (String) jdbcTemplate.queryForObject(passwordSql, new Object[]{userBean.getPassword()} ,String.class);
					
					String filePathSql = "SELECT  id FROM file_path WHERE flag = TRUE ";
					
					int fpath = -1 ;
					try {
						fpath = (Integer) jdbcTemplate.queryForObject(filePathSql, Integer.class);
					} catch (Exception e) {
						// TODO: handle exception
					}
					final int filePath = fpath ;
					
					userBean.setPassword(md5Password);
					final String sql = "INSERT INTO user (number, username, PASSWORD, dormitory_address, email, sex,mobile,user_type, " +
												"home_address, birthdate, zip_code, admission_time,faculty_id, specialty_id, " +
												"enrollment, image, grade, `status` ,descript,positional ,loginname ,file_path_id)VALUES(?,?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
					KeyHolder keyHolder = new GeneratedKeyHolder();  
					jdbcTemplate.update(  
							   new PreparedStatementCreator() {  
							    public PreparedStatement createPreparedStatement(Connection con) throws SQLException   
							    {  
								     	PreparedStatement ps =con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
								     	ps.setString(1, userBean.getNumber());
										ps.setString(2, userBean.getUserName());
										ps.setString(3, userBean.getPassword());
										ps.setString(4, userBean.getDormitoryAddress());
										ps.setString(5, userBean.getEmail());
										ps.setString(6, userBean.getSex());
										ps.setString(7, userBean.getMobile());
										ps.setString(8, userBean.getUserType() );
										ps.setString(9, userBean.getHomeAddress());
										ps.setString(10, userBean.getBirtdate());
										ps.setString(11, userBean.getZipCode());
										ps.setString(12, userBean.getAdmissionTime());
										ps.setString(13, userBean.getFacultyId());
										ps.setString(14, userBean.getSpecialtyId());
										ps.setFloat(15, userBean.getEnrollment());
										ps.setString(16, userBean.getImage());
										ps.setString(17, userBean.getGrade());
										ps.setInt(18, userBean.getStatus());
										ps.setString(19, userBean.getDescript());
										ps.setString(20, userBean.getPositional());
										ps.setString(21, userBean.getLogin_name());
										ps.setInt(22, filePath);
										
								     return ps;  
							    }  
							   }, keyHolder);  
					final int userId = keyHolder.getKey().intValue(); 
					
					
					
					int count = jdbcTemplate.queryForInt("SELECT count(id) FROM user where faculty_id=?  and year(admission_time)=? and user_type=? "  , new Object[]{userBean.getFacultyId() ,userBean.getAdmissionTime().substring(0, 4),userBean.getUserType()});
					String studentIndex = CCPContants.valueTransitionTo4(count);
					String studentNumber = userBean.getAdmissionTime().substring(0, 4) + faculty  + studentIndex ;
					log.info("---------------number = "+studentNumber);
					jdbcTemplate.update("UPDATE USER SET number = ? WHERE id = ?", new Object[]{studentNumber, userId});
									
					
					//赋予默认角色
					jdbcTemplate.update("INSERT INTO role_user (user_id ,role_id) VALUES ( ?, ?)" ,new Object[]{userId , 3});
					
					int len = teachGroupList.size();
					jdbcTemplate.update("delete from  user_teaching_group where user_id = ? ",new Object[]{userId});
					for(int i=0; i<len;i++){
						jdbcTemplate.update("INSERT INTO user_teaching_group (user_id ,teaching_group_id) VALUES ( ?, ?)",new Object[]{userId , teachGroupList.get(i)});
					}
					
					CCPContants.tranmitResultMap.put(index, userId);
				} catch (Exception e) {
					arg0.setRollbackOnly();
					log.error("create teacher failed : ", e);
					CCPContants.tranmitResultMap.put(index, -1);
				}
				return null;
			}
		});
		return (Integer) CCPContants.tranmitResultMap.remove(index) ;
	}
	
	public boolean checkPasswrodIsChange(int id ,String password){
		if(password.equals("*****************************************"))
			return false ;
		System.out.println(password);
		String sql = "SELECT count(1) FROM USER WHERE id = ? AND (PASSWORD = (SELECT PASSWORD(?)) or password = ?)";
		return jdbcTemplate.queryForInt(sql , new Object[]{id ,password ,password}) == 0;
	}

	public boolean update(final Object entity,final List<String> teachGropList , final boolean isChangePassword) {
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				 try{
					 UserBean userBean = (UserBean) entity;
					 String oldImage = findById(userBean.getId()).getImage();
					if(oldImage != null && !oldImage.equalsIgnoreCase(userBean.getImage())){
						File file = new File(CCPContants.SYS_PATH+File.separator+oldImage);
						if(file.exists()) file.delete(); 
					}
					 if(isChangePassword){
						 	String passwordSql = "select password(?)";
							String md5Password = (String) jdbcTemplate.queryForObject(passwordSql, new Object[]{userBean.getPassword()} ,String.class);
							
							userBean.setPassword(md5Password);
							String sql = "update  user set loginname = ?, username=?, PASSWORD=?, dormitory_address=?, email=?, sex=?,mobile=?,user_type=?, home_address=?, birthdate=?, zip_code=?, admission_time=?,faculty_id=?, specialty_id=?, enrollment=?, image=?, grade=?, `status`=? ,descript=?,positional = ?  where id = ?";
							jdbcTemplate.update(sql ,new Object[]{userBean.getLogin_name() ,userBean.getUserName(),userBean.getPassword(),
									userBean.getDormitoryAddress(),userBean.getEmail(),
									userBean.getSex(),userBean.getMobile(),
									userBean.getUserType() ,userBean.getHomeAddress(),
									userBean.getBirtdate(),userBean.getZipCode(),
									userBean.getAdmissionTime(),userBean.getFacultyId(),
									userBean.getSpecialtyId(),userBean.getEnrollment(),
									userBean.getImage(),userBean.getGrade(),
									userBean.getStatus(),userBean.getDescript() ,userBean.getPositional(),userBean.getId()});
						}else{
							String sql = "update  user set loginname = ? , username=?, dormitory_address=?, email=?, sex=?,mobile=?,user_type=?, home_address=?, birthdate=?, zip_code=?, admission_time=?,faculty_id=?, specialty_id=?, enrollment=?, image=?, grade=?, `status`=? ,descript=?,positional = ? where id = ?";
							jdbcTemplate.update(sql ,new Object[]{userBean.getLogin_name() ,userBean.getUserName(),
									userBean.getDormitoryAddress(),userBean.getEmail(),
									userBean.getSex(),userBean.getMobile(),
									userBean.getUserType() ,userBean.getHomeAddress(),
									userBean.getBirtdate(),userBean.getZipCode(),
									userBean.getAdmissionTime(),userBean.getFacultyId(),
									userBean.getSpecialtyId(),userBean.getEnrollment(),
									userBean.getImage(),userBean.getGrade(),
									userBean.getStatus(),userBean.getDescript(),userBean.getPositional(),userBean.getId()});
							
						}
					 	int len = teachGropList.size();
						jdbcTemplate.update("delete from  user_teaching_group where user_id = ? ",new Object[]{userBean.getId()});
						for(int i=0; i<len;i++){
							jdbcTemplate.update("INSERT INTO user_teaching_group (user_id ,teaching_group_id) VALUES ( ?, ?)",new Object[]{userBean.getId() , teachGropList.get(i)});
						}
						
				 }catch(Exception e){
					 arg0.setRollbackOnly();
				 }
				 return null;
			}
		});
		
		return true;
		
	}

	public boolean delete(int id) {
//		String sql = "delete from user where id = ?";
		String sql = "update user set status = -1 where id = ?";
		return jdbcTemplate.update(sql ,new Object[]{id}) > 0;
	}

	/**
	 * 恢复用户登录状态
	 * @param userId
	 * @return
	 */
	public void recovery(int userId){
		String sql = "update user set status = 0 where id = ?";
		jdbcTemplate.update(sql ,new Object[]{userId});
	}
	
	
	
	public void deleteTeacher(final int userId){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					
					//删除role_user表
					String sql = "delete from role_user where user_id = ?";
					jdbcTemplate.update(sql ,new Object[]{userId});
					//删除课程试题
					sql = "delete from training where is_chapter = '1' and (chapter_course_id in (select id FROM courses  where teacher_id = ?))  ";
					jdbcTemplate.update(sql , new Object[]{userId});
				 
					//删除章节信息
					sql = "delete from courses_detail where courses_id in (select id from courses  where teacher_id = ?)";
					jdbcTemplate.update(sql , new Object[]{userId});
					
					//删除courses_need_approve
					sql = "delete from courses_need_approve where courses_id in (select id from courses  where teacher_id = ?)";
					jdbcTemplate.update(sql , new Object[]{userId});
					//删除课程信息
					sql = "delete from courses where teacher_id = ?";
					jdbcTemplate.update(sql , new Object[]{userId});
					//删除用户表
					 sql = "delete from user where id = ?";
					jdbcTemplate.update(sql ,new Object[]{userId});
					
					//删除课程文件
				} catch (Exception e) {
					arg0.setRollbackOnly();
					log.error("delete teacher["+userId+"] failed :", e);
				}
				return null;
			}
		});
	}
	
	
	public UserBean findById(int id) {
		String sql = "SELECT u.* ,f.facultyname  FROM USER u LEFT JOIN faculty f ON f.id = u.faculty_id where u.id = ?" ;
		return (UserBean)jdbcTemplate.queryForObject(sql, new Object[] { id }, new UsersMapper());
	}
	//： 能够登录，作为老师不能直接发布课程，需领导审批后发布的课程才能被学生选择为在线学习 1： 作为老师可以直接发布课程
	public void approvalAuthority(String id ,String appA){
		String sql = "update USER set `status` = ? where id = ?";
		jdbcTemplate.update(sql ,new Object[]{appA , id});
	}
	
	public String getSpecialtyIdById(String id){
		String sql = "SELECT specialty_id FROM USER WHERE id = ? ";
		return (String) jdbcTemplate.queryForObject(sql, new Object[]{id} , String.class);
	}
	
	
//	public List pageQuery(int start, int pagesize) {
//		String sql = "SELECT u.* ,f.facultyname  FROM USER u LEFT JOIN faculty f ON f.id = u.faculty_id limit ?,?";
//		return jdbcTemplate.query(sql, new Object[] { start,pagesize},new UsersMapper());
//	}

	public int getDisableCount(){
		String sql = "select count(1) from USER u where u.status = -1";
		return jdbcTemplate.queryForInt(sql);
	}
	
	public List queryDisableUsers(int start, int pagesize ){
		String sql = "SELECT u.* ,f.facultyname  FROM USER u LEFT JOIN faculty f ON f.id = u.faculty_id where u.status = -1 limit ?,?";
		return jdbcTemplate.query(sql, new Object[] { start,pagesize},new UsersMapper());
	}
	
	//获取所有发布课程老师数据
	public List queryReleaseAllTeachers(){
		String sql = "SELECT DISTINCT u.* FROM courses c "+
					" LEFT JOIN USER u ON u.id = c.teacher_id "+
					" WHERE c.status IN (4,5);";
		return jdbcTemplate.query(sql, new UserTeachers());
	}
	
	
	
	public List queryAllTeachers(){
		String sql = "SELECT u.id,u.username  FROM USER u  where u.status != -1 and u.id > 1";
		return jdbcTemplate.query(sql, new UserTeachers());
	}
	
	
	
	public int getCount( String number ,String  searchUserType ,String  searchStatus,String  searchLoginUserName,String searchFacultyId ,String  searchSpecialtyId) {
		String sql = "select count(1) from USER u ";
		StringBuffer sb = new StringBuffer() ;
		if(number != null  && !number.equals(""))
			sb.append(" u.number = '"+number+"' ");
		if(searchUserType != null  && !searchUserType.equals("") && !searchUserType.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.user_type = "+searchUserType);
		}
		if(searchStatus != null  && !searchStatus.equals("") && !searchStatus.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.status = "+searchStatus);
		}
		if(searchLoginUserName != null  && !searchLoginUserName.equals("") ){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.username like '%"+searchLoginUserName+"%' ");
		}
		if(searchFacultyId != null  && !searchFacultyId.equals("") && !searchFacultyId.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.faculty_id = "+searchFacultyId);
		}
		if(searchSpecialtyId != null  && !searchSpecialtyId.equals("") && !searchSpecialtyId.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" (u.specialty_id = "+searchSpecialtyId + " or u.specialty_id lik '%:"+searchSpecialtyId+"%' or u.specialty_id lik '%"+searchSpecialtyId+":%')");
		}
		sql = sql + "  where u.user_type != 1 and u.status != -1 ";
		if(sb.length() > 0)
			sql = sql + " and " + sb.toString() ;
		return jdbcTemplate.queryForInt(sql);
	}
	public List pageQuery(int start, int pagesize , String number ,String  searchUserType ,String  searchStatus,String  searchLoginUserName,String searchFacultyId ,String  searchSpecialtyId) {
		String sql = "SELECT u.* ,f.facultyname  FROM USER u LEFT JOIN faculty f ON f.id = u.faculty_id ";
		
		StringBuffer sb = new StringBuffer() ;
		if(number != null  && !number.equals(""))
			sb.append(" u.number = '"+number+"' ");
		if(searchUserType != null  && !searchUserType.equals("") && !searchUserType.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.user_type = "+searchUserType);
		}
		if(searchStatus != null  && !searchStatus.equals("") && !searchStatus.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.status = "+searchStatus);
		}
		if(searchLoginUserName != null  && !searchLoginUserName.equals("") ){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.username like '%"+searchLoginUserName+"%' ");
		}
		if(searchFacultyId != null  && !searchFacultyId.equals("") && !searchFacultyId.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" u.faculty_id = "+searchFacultyId);
		}
		if(searchSpecialtyId != null  && !searchSpecialtyId.equals("") && !searchSpecialtyId.equals("-1")){
			if(sb.length() > 0)
				sb.append(" and ");
			sb.append(" (u.specialty_id = "+searchSpecialtyId + " or u.specialty_id lik '%:"+searchSpecialtyId+"%' or u.specialty_id lik '%"+searchSpecialtyId+":%')");
		}
		sql = sql + " where u.user_type != 1 and u.status != -1 " ;
		if(sb.length()> 0)
			sql = sql +" and "+ sb.toString() ;
		sql = sql  +" limit ?,?" ;
		return jdbcTemplate.query(sql, new Object[] { start,pagesize},new UsersMapper());
	}
	
	public ResourseBean getResourseByID(String strId){
		String sql = "SELECT * FROM resource_type WHERE id = ?";
		return (ResourseBean) jdbcTemplate.queryForObject(sql, new Object[]{strId},  new ResourseMapper());
	}
	
	
	class UserTeachers implements RowMapper{
		@Override
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			UserBean userBean = new UserBean();
			userBean.setId(rs.getInt("id"));
			userBean.setUserName(rs.getString("username"));
			return userBean;
		}
		
	}
	
	
	public class UsersMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			UserBean userBean = new UserBean();
			userBean.setId(rs.getInt("id"));
			userBean.setNumber(rs.getString("number"));
			userBean.setLogin_name(rs.getString("loginname"));
			userBean.setUserName(rs.getString("username"));
			String password = rs.getString("password") ;
			userBean.setShowPassword(password);
			StringBuffer sb = new StringBuffer();
			for (int j = 0; j < password.length(); j++) 
				sb.append("*");
			userBean.setPassword(sb.toString());
			userBean.setDormitoryAddress(rs.getString("dormitory_address"));
			userBean.setEmail(rs.getString("email"));
			userBean.setSex(rs.getString("sex"));
			if(userBean.getSex().equals("1")){
				userBean.setSexName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_SEX_MAN));
			}else if(userBean.getSex().equals("0")){
				userBean.setSexName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_SEX_WOMAN));
			}
			userBean.setMobile(rs.getString("mobile"));
			userBean.setUserType(rs.getString("user_type"));
			
//			if(userBean.getUserType() != null  && !userBean.getUserType().equals("")){
//				try {
//					ResourseBean be = getResourseByID(userBean.getUserType());
//					if(be != null && be.getType().equals(Contants.DATA_DICTIONARY_TEACHER_TYPE)){
//						userBean.setUserTypeName(be.getTypeName());
//					}
//				} catch (Exception e) {
//				}
//			}
//			if(userBean.getUserType() == null || userBean.getUserType().equals("")){
//				if(userBean.getUserType().equals("1")){
//					userBean.setUserTypeName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_STUDENT));
//				}else if(userBean.getUserType().equals("2")){
//					userBean.setUserTypeName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_FULL_TIME_TEACHER));
//				}else if(userBean.getUserType().equals("3")){
//					userBean.setUserTypeName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_PART_TIME_TEACHER));
//				}else if(userBean.getUserType().equals("4")){
//					userBean.setUserTypeName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_EXT_TEACHER));
//				}
//			}
			userBean.setHomeAddress(rs.getString("home_address"));
			userBean.setBirtdate(rs.getString("birthdate"));
			if(userBean.getBirtdate() != null  && !userBean.getBirtdate().equals(""))
				userBean.setBirtdate(userBean.getBirtdate().substring(0,10));
			userBean.setZipCode(rs.getString("zip_code"));
			userBean.setAdmissionTime(rs.getString("admission_time"));
			if(userBean.getAdmissionTime() != null  && !userBean.getAdmissionTime().equals(""))
				userBean.setAdmissionTime(userBean.getAdmissionTime().substring(0,10));
			userBean.setFacultyId(rs.getString("faculty_id"));
			userBean.setSpecialtyId(rs.getString("specialty_id"));
			userBean.setEnrollment(rs.getFloat("enrollment"));
			userBean.setImage(rs.getString("image"));
			userBean.setGrade(rs.getString("grade"));
			userBean.setStatus(rs.getInt("status"));
			if(userBean.getStatus() == 0){
				userBean.setStatusVal(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_OK));
			}else if(userBean.getStatus() == -1){
				userBean.setStatusVal(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_DELETE));
			}else if(userBean.getStatus() == -2){
				userBean.setStatusVal(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_NO));
			}else{
				userBean.setStatusVal(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_OK));
			}
			userBean.setFaculty(rs.getString("facultyname"));
			userBean.setDescript(rs.getString("descript"));
			
			if(userBean.getStatus() == 0){
				userBean.setApprowalAuthority(BaseAction.getInstance().getMessages("btn_no"));
			}else if(userBean.getStatus() == 1){
				userBean.setApprowalAuthority(BaseAction.getInstance().getMessages("btn_yes"));
			}
			userBean.setPositional(rs.getString("positional"));
			userBean.setFile_path_id(rs.getInt("file_path_id"));
			return userBean;
		}
	}
	/**
	 * 资源封装类
	 * @author Administrator
	 *
	 */
	public class ResourseMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			ResourseBean rolesBean = new ResourseBean();
			rolesBean.setId(rs.getInt("id"));
			rolesBean.setName(rs.getString("resource_type_name"));
			rolesBean.setDescription(rs.getString("resource_type_des"));
			rolesBean.setType(rs.getString("resource_type"));
			try {
				if(rolesBean.getType().equalsIgnoreCase(String.valueOf(Contants.DATA_DICTIONARY_CLASS_TYPE)))
					rolesBean.setTypeName(BaseAction.getInstance().getMessages("pages_resourse_type_course"));
				else if(rolesBean.getType().equalsIgnoreCase(String.valueOf(Contants.DATA_DICTIONARY_LANGUAGE_TYPE)))
					rolesBean.setTypeName(BaseAction.getInstance().getMessages("course_page_course_language_type"));
				else
					rolesBean.setTypeName(BaseAction.getInstance().getMessages("pages_resourse_type_other"));
			} catch (Exception e) {
			}
			return rolesBean;
		}
		
	}
}
